package tools;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class DBHelper {
    private static Connection connection = null;
    private static PreparedStatement preparedStatement = null;
    private static ResultSet resultSet = null;
    private static String url = "jdbc:mysql://localhost:3306/skdb";


    //创建连接
    private static Connection getConn() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(url, "root", "root");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return connection;
    }

    //关闭
    private static void closeAll() {
        try {
            if (resultSet != null) {
                resultSet.close();
            }
            if (preparedStatement != null) {
                preparedStatement.close();
            }
            if (connection != null) {
                connection.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //查询
    public static List<Map<String, Object>> query(String sql,int pre,int post) {
        try {
            connection = getConn();
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setInt(1,pre);
            preparedStatement.setInt(2,post);
            resultSet=preparedStatement.executeQuery();
            List<Map<String, Object>> data = new ArrayList<>();
            while (resultSet.next()) {
                //获取各个列
                ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
                int columnCount = resultSetMetaData.getColumnCount();
                HashMap<String, Object> row = new HashMap<>();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = resultSetMetaData.getColumnName(i);
                    row.put(columnName, resultSet.getObject(i));
                }
                data.add(row);
            }
            return data;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return null;
    }


}
